package data

import (
	"commerce/common"
	"commerce/model"
	"database/sql"
	"fmt"
	"strings"
	"time"
)

func AddOrder(tx *sql.Tx, o model.Order) (int, error) {

	stmt, err := tx.Prepare(`insert into orders (order_no, create_time, update_time, total_count, total_amount, state, 
                    user_id, pay_amount, coupon_amount, integration_amount, discount_amount, freight_amount, 
                    gift_integration, gift_growth, auto_confirm_day, integration, receiver_user_name, receiver_phone, 
                    receiver_post_code, receiver_province, receiver_city, receiver_region, receiver_detail_addr, is_valid) 
                    values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`)
	if err != nil {
		return 0, err
	}
	defer stmt.Close()

	result, err := stmt.Exec(o.OrderNo, o.CreateTime, o.UpdatedTime, o.TotalCount, o.TotalAmount, o.State, o.UserId, o.PayAmount,
		o.CouponAmount, o.IntegrationAmount, o.DiscountAmount, o.FreightAmount,
		o.GiftIntegration, o.GiftGrowth, o.AutoConfirmDay, o.Integration, o.ReceiverUserName, o.ReceiverPhone,
		o.ReceiverPostCode, o.ReceiverProvince, o.ReceiverCity, o.ReceiverRegion, o.ReceiverDetailAddr, o.IsValid)

	if err != nil {
		return 0, fmt.Errorf("save order err: %v", err)
	}
	id, err := result.LastInsertId()

	if err != nil {
		return 0, fmt.Errorf("save order insert id err: %v", err)
	}
	return int(id), nil
}

func BatchAddOrderItem(tx *sql.Tx, items []model.OrderItem, orderId int) error {

	stmt, err := tx.Prepare(`insert into order_items(count, amount, sku_price, order_id, order_no, spu_id, 
                        spu_name, sku_id, sku_name, sku_img, sku_attr_value, brand_id, coupon_amount, 
                        integration, integration_amount, discount_amount, gift_integration, gift_growth, real_amount, updated_time) 
                        values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`)
	if err != nil {
		return err
	}
	defer stmt.Close()

	for _, i := range items {
		_, err = stmt.Exec(i.Count, i.Amount, i.SkuPrice, orderId, i.OrderNo, i.SpuId,
			i.SpuName, i.SkuId, i.SkuName, i.SkuImg, i.SkuAttrValue, i.BrandId, i.CouponAmount,
			i.Integration, i.IntegrationAmount, i.DiscountAmount, i.GiftIntegration, i.GiftGrowth, i.RealAmount, time.Now().Add(8*time.Hour))
		if err != nil {
			return err
		}
	}
	return nil
}

func AddOrderStatus(tx *sql.Tx, orderId int, orderState int8) error {

	stmt, err := tx.Prepare(`insert into order_status (order_id, status, created_time) values(?,?,?)`)
	if err != nil {
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(orderId, orderState, time.Now().Add(8*time.Hour))

	return err
}

func AddOrderStatus2(orderId int, orderState int8) error {

	stmt, err := common.DB.Prepare(`insert into order_status (order_id, status, created_time) values(?,?,?)`)
	if err != nil {
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(orderId, orderState, time.Now().Add(8*time.Hour))

	return err
}

func GetOrderById(orderId int) (*model.Order, error) {

	stmt, err := common.DB.Prepare("SELECT id, user_id, `state` FROM orders where id = ?")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	var o model.Order
	if err = stmt.QueryRow(orderId).Scan(&o.Id, &o.UserId, &o.State); err != nil {
		return nil, err
	}
	return &o, nil
}

// 后台订单管理

func ListV2Order(pageNo, pageSize int, orderNo string) (*common.Page, error) {

	whereSql := composeSearchOrderQuerySql(orderNo)

	row := common.DB.QueryRow("select count(*) FROM orders" + whereSql)
	var totalRecords int
	if err := row.Scan(&totalRecords); err != nil {
		return nil, err
	}
	stmt, err := common.DB.Prepare(`select id, order_no, create_time, total_count, total_amount, 
		state, pay_amount, discount_amount, freight_amount, 
		receiver_user_name, receiver_phone, receiver_province, receiver_city, receiver_region, receiver_detail_addr, is_valid from orders` +
		whereSql + " LIMIT ?, ?")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query((pageNo-1)*pageSize, pageSize)
	if err != nil {
		return nil, err
	}
	var orders []model.Order
	for rows.Next() {
		var o model.Order
		if err := rows.Scan(&o.Id, &o.OrderNo, &o.CreatedTimeStr, &o.TotalCount, &o.TotalAmount, &o.State, &o.PayAmount,
			&o.DiscountAmount, &o.FreightAmount, &o.ReceiverUserName, &o.ReceiverPhone,
			&o.ReceiverProvince, &o.ReceiverCity, &o.ReceiverRegion, &o.ReceiverDetailAddr, &o.IsValid); err != nil {
			return nil, err
		}
		o.Addr = fmt.Sprintf("%s/%s/%s/%s", o.ReceiverProvince, o.ReceiverCity, o.ReceiverRegion, o.ReceiverDetailAddr)
		o.StateStr = translateState(o.State)
		orders = append(orders, o)
	}
	return common.NewPage(orders, pageNo, pageSize, totalRecords), nil
}

func translateState(state int8) string {

	switch state {
	case common.ORDER_STATE_CREATED:
		return "创建"
	case common.ORDER_STATE_PAID:
		return "已支付"
	case common.ORDER_STATE_DELIVERING:
		return "配送中"
	case common.ORDER_STATE_RECEIVED:
		return "已收货"
	case common.ORDER_STATE_FINISHED:
		return "已完成"
	case common.ORDER_STATE_CANCELLED:
		return "已取消"
	case common.ORDER_STATE_REFUND:
		return "已退款"
	case common.ORDER_STATE_RETURN:
		return "已退货"
	case common.ORDER_STATE_DEL:
		return "已删除"
	default:
		return "未知"
	}
}

// 订单详情

func GetOrderDetailById(orderId int) (*model.Order, error) {

	stmt, err := common.DB.Prepare(`SELECT id, order_no, create_time, update_time, total_count, total_amount, state, 
	user_id, pay_amount, coupon_amount, integration_amount, discount_amount, freight_amount, gift_integration, gift_growth,
       auto_confirm_day, integration, receiver_user_name, receiver_phone, receiver_post_code, receiver_province, 
       receiver_city, receiver_region, receiver_detail_addr, is_valid FROM orders where id = ?`)
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	var o model.Order
	if err = stmt.QueryRow(orderId).Scan(&o.Id, &o.OrderNo, &o.CreatedTimeStr, &o.UpdatedTimeStr, &o.TotalCount, &o.TotalAmount, &o.State,
		&o.UserId, &o.PayAmount, &o.CouponAmount, &o.IntegrationAmount, &o.DiscountAmount, &o.FreightAmount,
		&o.GiftIntegration, &o.GiftGrowth, &o.AutoConfirmDay, &o.Integration, &o.ReceiverUserName, &o.ReceiverPhone,
		&o.ReceiverPostCode, &o.ReceiverProvince, &o.ReceiverCity, &o.ReceiverRegion, &o.ReceiverDetailAddr, &o.IsValid); err != nil {
		return nil, err
	}
	o.StateStr = translateState(o.State)
	return &o, nil
}

func ListOrderItemByOrderId(orderId int) ([]model.OrderItem, error) {

	stmt, err := common.DB.Prepare(`select id, count, amount, sku_price, spu_name, sku_id, sku_name, sku_img, sku_attr_value, 
       coupon_amount, integration, integration_amount, discount_amount, gift_integration, gift_growth, real_amount
		FROM order_items WHERE order_id = ?`)

	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query(orderId)
	if err != nil {
		return nil, err
	}
	var items []model.OrderItem
	for rows.Next() {
		var i model.OrderItem
		if err := rows.Scan(&i.Id, &i.Count, &i.Amount, &i.SkuPrice,
			&i.SpuName, &i.SkuId, &i.SkuName, &i.SkuImg, &i.SkuAttrValue, &i.CouponAmount,
			&i.Integration, &i.IntegrationAmount, &i.DiscountAmount, &i.GiftIntegration, &i.GiftGrowth, &i.RealAmount); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	return items, nil
}

func ListOrderStatusByOrderId(orderId int) ([]model.OrderStatus, error) {

	stmt, err := common.DB.Prepare(`select status, created_time FROM order_status WHERE order_id = ?`)

	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query(orderId)
	if err != nil {
		return nil, err
	}
	var oss []model.OrderStatus
	for rows.Next() {
		var os model.OrderStatus
		if err := rows.Scan(&os.Status, &os.CreatedTimeStr); err != nil {
			return nil, err
		}
		os.StatusStr = translateState(os.Status)
		oss = append(oss, os)
	}
	return oss, nil
}

func UpdateOrderValid(orderId, isValid int) error {

	stmt, err := common.DB.Prepare("UPDATE orders SET is_valid = ? WHERE id = ?")
	if err != nil {
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(isValid, orderId)
	return err
}

func UpdateOrderStatus(orderId, status int) error {

	stmt, err := common.DB.Prepare("UPDATE orders SET `state` = ? WHERE id = ?")

	if err != nil {
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(status, orderId)
	return err
}

func UpdateOrderStatusTx(tx *sql.Tx, orderId, status int) error {

	stmt, err := tx.Prepare("UPDATE orders SET `state` = ? WHERE id = ?")

	if err != nil {
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(status, orderId)
	return err
}

func composeSearchOrderQuerySql(orderNo string) string {

	// 没有条件查询
	if len(orderNo) == 0 {
		return ""
	}
	sb := strings.Builder{}
	// 前缀匹配
	sb.WriteString(" WHERE order_no like '" + strings.TrimSpace(orderNo) + "%'")

	return sb.String()
}

// app功能使用------------------------------------------

func MyOrders(memberId, pageNo, pageSize int) (*common.Page, error) {

	stmt, err := common.DB.Prepare("select count(*) FROM orders where user_id = ? and is_valid = 1")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	var totalRecords int
	if err := stmt.QueryRow(memberId).Scan(&totalRecords); err != nil {
		return nil, err
	}
	stmt, err = common.DB.Prepare(`select id, order_no, state, pay_amount from orders where user_id = ? and is_valid = 1 order by id desc` + " LIMIT ?, ?")
	if err != nil {
		return nil, err
	}
	rows, err := stmt.Query(memberId, (pageNo-1)*pageSize, pageSize)
	if err != nil {
		return nil, err
	}
	var orderIds []int
	var orderMap = make(map[int]model.Order, pageSize)
	for rows.Next() {
		var o model.Order
		if err := rows.Scan(&o.Id, &o.OrderNo, &o.State, &o.PayAmount); err != nil {
			return nil, err
		}
		orderIds = append(orderIds, o.Id)
		o.StateStr = translateState(o.State)

		orderMap[o.Id] = o
	}
	// 批量查询订单明细
	orderItemList, err := listOrderItemByOrderIds(orderIds)
	if err != nil {
		return nil, err
	}
	var ok bool
	var o model.Order
	for _, i := range orderItemList {

		if o, ok = orderMap[i.OrderId]; ok {
			o.OrderItemList = append(o.OrderItemList, i)
			orderMap[i.OrderId] = o
		}
	}
	var orders []model.Order
	for _, id := range orderIds {
		orders = append(orders, orderMap[id])
	}
	return common.NewPage(orders, pageNo, pageSize, totalRecords), nil
}

func listOrderItemByOrderIds(orderIds []int) ([]model.OrderItem, error) {

	stmt, err := common.DB.Prepare(`select id, order_id, count, sku_id, sku_name, sku_img FROM order_items WHERE order_id in (?` + strings.Repeat(",?", len(orderIds)-1) + `)`)
	if err != nil {
		return nil, err
	}
	defer stmt.Close()
	args := make([]interface{}, len(orderIds))
	for i, id := range orderIds {
		args[i] = id
	}
	rows, err := stmt.Query(args...)
	if err != nil {
		return nil, err
	}
	var items []model.OrderItem
	for rows.Next() {
		var i model.OrderItem
		if err := rows.Scan(&i.Id, &i.OrderId, &i.Count, &i.SkuId, &i.SkuName, &i.SkuImg); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	return items, nil
}

func OrderDetail(orderId int, memberId int) (*model.Order, error) {

	stmt, err := common.DB.Prepare(`select id, order_no, state, pay_amount, total_amount, freight_amount, create_time, receiver_user_name, receiver_phone, receiver_province, 
       receiver_city, receiver_region, receiver_detail_addr from orders where id = ? and user_id = ? and is_valid = 1`)
	if err != nil {
		return nil, err
	}
	var o model.Order
	err = stmt.QueryRow(orderId, memberId).Scan(&o.Id, &o.OrderNo, &o.State, &o.PayAmount, &o.TotalAmount, &o.FreightAmount, &o.CreatedTimeStr, &o.ReceiverUserName, &o.ReceiverPhone,
		&o.ReceiverProvince, &o.ReceiverCity, &o.ReceiverRegion, &o.ReceiverDetailAddr)
	if err != nil {
		return nil, err
	}
	orderItems, err := ListAppOrderItemByOrderId(o.Id, true)
	if err != nil {
		return nil, err
	}
	o.StateStr = translateState(o.State)

	o.OrderItemList = orderItems

	return &o, nil
}

func ListAppOrderItemByOrderId(orderId int, detailFlag bool) ([]model.OrderItem, error) {

	var stmt *sql.Stmt
	var err error

	if detailFlag {
		stmt, err = common.DB.Prepare(`select id, count, sku_id, sku_name, sku_img, sku_attr_value, sku_price, commented FROM order_items WHERE order_id = ?`)
	} else {
		stmt, err = common.DB.Prepare(`select id, count, sku_id, sku_name, sku_img FROM order_items WHERE order_id = ?`)
	}
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query(orderId)
	if err != nil {
		return nil, err
	}
	var items []model.OrderItem
	if detailFlag {
		for rows.Next() {
			var i model.OrderItem
			if err := rows.Scan(&i.Id, &i.Count, &i.SkuId, &i.SkuName, &i.SkuImg, &i.SkuAttrValue, &i.SkuPrice, &i.Commented); err != nil {
				return nil, err
			}
			items = append(items, i)
		}
	} else {
		for rows.Next() {
			var i model.OrderItem
			if err := rows.Scan(&i.Id, &i.Count, &i.SkuId, &i.SkuName, &i.SkuImg); err != nil {
				return nil, err
			}
			items = append(items, i)
		}
	}
	return items, nil
}

func ListOrderStatusByIdAndUserId(orderId, userId int) (*model.Order, error) {

	stmt, err := common.DB.Prepare("select `state` FROM orders WHERE id = ? and user_id = ?")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	var o model.Order
	if err = stmt.QueryRow(orderId, userId).Scan(&o.State); err != nil {
		return nil, err
	}
	return &o, nil
}

func UpdateUserOrderStatus(userId, orderId int, toStatus int8) error {

	stmt, err := common.DB.Prepare("UPDATE orders SET `state` = ?, update_time = ? WHERE id = ? AND user_id = ?")
	if err != nil {
		return err
	}
	defer stmt.Close()
	result, err := stmt.Exec(toStatus, time.Now().Add(8*time.Hour), orderId, userId)
	if err != nil {
		return err
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return err
	}
	if affected < 1 {
		return fmt.Errorf("系统异常！")
	}
	return nil
}

func GetOrderItemByOrderIdAndSkuId(orderId, skuId int) (*model.OrderItem, error) {

	stmt, err := common.DB.Prepare(`select id, spu_id, spu_name, sku_name, sku_attr_value, commented 
		FROM order_items WHERE order_id = ? and sku_id = ?`)

	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	var i model.OrderItem
	if err = stmt.QueryRow(orderId, skuId).Scan(&i.Id, &i.SpuId, &i.SpuName, &i.SkuName, &i.SkuAttrValue, &i.Commented); err != nil {
		return nil, err
	}
	return &i, nil
}

func CountCommentedOrderItemByOrderId(tx *sql.Tx, orderId int) (bool, error) {

	row := tx.QueryRow(`select count(*) FROM order_items WHERE order_id = ? and commented = 0`, orderId)
	var totalRecords int
	if err := row.Scan(&totalRecords); err != nil {
		return false, err
	}
	return totalRecords == 0, nil
}
